import openpyxl,os,poplib,smtplib,datetime,base64
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.parser import Parser
from email.header import decode_header
from email.utils import parseaddr

name_list = {
    '李争华': 'lizhenghua@hisense.com',
    '胡高祥': 'hugaoxiang@hisense.com',
    '郑源富': 'zhengyuanfu@hisense.com',
    '周景耀': 'zhoujingyao@hisense.com',
    '甘佐前': 'ganzuoqian@hisense.com',
    '王青': 'wangqing9@hisense.com',
    '罗仕新': 'luoshixin@hisense.com',
    '肖兰': 'xiaolan@hisense.com',
    '黄冠辉': 'huangguanhui@hisense.com',
    '霍佳胜': 'huojiasheng@hisense.com',
    '何伟忠': 'heweizhong@hisense.com',
    '莫海林': 'mohailin@hisense.com',
    '韩国彪': 'hanguobiao@hisense.com',
    '伍志森': 'wuzhisen@hisense.com',
    '郑秋利': 'zhengqiuli@hisense.com',
    '黄永勤': 'huangyongqin@hisense.com',
    '163邮箱':'huang.yongqin@163.com'
}



class SendMail(object):
    def __init__(self,recv,title,content,username='huang.yongqin@qq.com',passwd='udckfaycpwynbhbi',
                 file=None,ssl=False,
                 email_host='smtp.qq.com',port=25,ssl_port=465):
        '''
        :param username: 用户名
        :param passwd: 密码
        :param recv: 收件人，多个要传list ['a@qq.com','b@qq.com]
        :param title: 邮件标题
        :param content: 邮件正文
        :param file: 附件路径，如果不在当前目录下，要写绝对路径，默认没有附件
        :param ssl: 是否安全链接，默认为普通
        :param email_host: smtp服务器地址，默认为163服务器
        :param port: 非安全链接端口，默认为25
        :param ssl_port: 安全链接端口，默认为465
        '''
        self.username = username #用户名
        self.passwd = passwd #密码
        self.recv = recv #收件人，多个要传list ['a@qq.com','b@qq.com]
        self.title = title #邮件标题
        self.content = content #邮件正文
        self.file = file #附件路径，如果不在当前目录下，要写绝对路径
        self.email_host = email_host #smtp服务器地址
        self.port = port #普通端口
        self.ssl = ssl #是否安全链接
        self.ssl_port = ssl_port #安全链接端口

    def send_mail(self):
        msg = MIMEMultipart()
        #发送内容的对象
        if self.file:#处理附件的
            file_name = os.path.split(self.file)[-1]#只取文件名，不取路径
            try:
                f = open(self.file, 'rb').read()
            except Exception as e:
                print('附件打不开')
                #raise Exception('附件打不开！！！！')
            else:
                att = MIMEText(f,"base64", "utf-8")
                att["Content-Type"] = 'application/octet-stream'
                #base64.b64encode(file_name.encode()).decode()
                new_file_name='=?utf-8?b?' + base64.b64encode(file_name.encode()).decode() + '?='
                #这里是处理文件名为中文名的，必须这么写
                att["Content-Disposition"] = 'attachment; filename="%s"'%(new_file_name)
                msg.attach(att)
        msg.attach(MIMEText(self.content))#邮件正文的内容
        msg['Subject'] = self.title  # 邮件主题
        msg['From'] = self.username  # 发送者账号
        msg['To'] = ','.join(self.recv)  # 接收者账号列表
        if self.ssl:
            self.smtp = smtplib.SMTP_SSL(self.email_host,port=self.ssl_port)
        else:
            self.smtp = smtplib.SMTP(self.email_host,port=self.port)
        #发送邮件服务器的对象
        self.smtp.login(self.username,self.passwd)
        try:
            self.smtp.sendmail(self.username,self.recv,msg.as_string())
            pass
        except Exception as e:
            print('出错了。。'+str(datetime.datetime.now()),e)
        else:
            print('发送成功！'+str(datetime.datetime.now()))
        self.smtp.quit()


class Plan(object):
    def __init__(self, filename):
        self.file = filename
        self.flag = False
        self.loadfile()
        self.blank_col_next = self.get_blank_col(1, self.sheet_next)
        self.blank_row_next = self.get_blank_row(3, self.sheet_next)
        self.blank_col_now = self.get_blank_col(1, self.sheet_now)
        self.blank_row_now = self.get_blank_row(3, self.sheet_now)
        #print(self.dict)

    def loadfile(self):
        if os.path.splitext(self.file)[-1] == '.xlsx':
            try:
                self.wb = openpyxl.load_workbook(self.file)
                self.sheet_now = self.wb['本周工作计划']
                self.sheet_next = self.wb['下周工作计划']
                return True
            except Exception as e:
                print(e)
                print('附件错误，请确认附件结构是否修改过')
                return False
        else:
            print('附件不是"xlsx"格式，请确认!')
            return False

    def get_blank_row(self, column, sheet):
        for i in range(1, sheet.max_row + 2):
            if sheet.cell(row=i, column=column).value is None:
                print('表格"%s"的第%s行是空白的。' % (sheet.title, i))
                break
        return i

    def get_blank_col(self, row, sheet):
        for i in range(1, 20):
            if sheet.cell(row=row, column=i).value is None:
                print(('表格"%s"的第%s列是空白的。' % (sheet.title, i)))
                break
        return i

    def set_blank(self, end_row, end_col, sheet, start_row=2, start_col=2):
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col+2):
                sheet.cell(row=i, column=j).value = None

    def load_dict(self, sheet, end_row, end_col, start_row=1, start_col=2):
        dict = {}
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col):
                dict.setdefault((i, j), sheet.cell(row=i, column=j).value)
        return dict

    def copy(self, end_row, end_col, target_sheet, source_sheet, start_row=2, start_col=2):
        self.set_blank(self.blank_row_now, self.blank_col_now, target_sheet)
        for i in range(start_row, end_row+1):
            for j in range(start_col, end_col+1):
                target_sheet.cell(row=i, column=j).value = source_sheet.cell(row=i, column=j).value
        self.set_blank(self.blank_row_next, self.blank_col_next, source_sheet)

    def compare(self, target_sheet, source_sheet):
        pass

    def save(self):
        self.wb.save(self.file)

def send(addr_list, mail_title, mail_content, filename=None):
    m = SendMail(recv=addr_list, title=mail_title, file=filename, content=mail_content,
                 ssl=True, )
    m.send_mail()

def remind_plan(name_list):
    for name, email in name_list.items():
        addr_list = [email]
        print(addr_list)
        title = '提醒，请在周六下班前提交工作计划。具体请见附件'
        filename = os.path.join('./工作计划/上周工作计划/', (name + '.xlsx'))
        if os.path.isfile(filename):
            content = name[0] + '工：\n' + '请点检上周的工作计划完成情况，并提交下周的工作计划。\n' \
                                         '请在“本周工作点检”表内点检本周的工作，并在“下周工作计划”表内填写下周的工作计划。\n' \
                                         '附件是你上周提交的工作计划，请下载附件并填写回复。\n谢谢！'
        else:
            filename = os.path.join('./工作计划/工作计划模板/', '工作计划模板.xlsx')
            content = name[0] + '工：\n' + '你上周未提交工作计划。\n    请提交下周的工作计划。\n' \
                                         '请在“本周工作点检”表内点检本周的工作，并在“下周工作计划”表内填写下周的工作计划。\n' \
                                         '附件是工作计划的样板，请下载附件并填写回复。\n谢谢！'
        #send(addr_list, title, content, filename)


if __name__ == '__main__':
    # plan = Plan('D:\Project\工作计划.xlsx')
    # plan.copy(end_row=plan.blank_row_next, end_col=plan.blank_col_next, source_sheet=plan.sheet_next,
    #           target_sheet=plan.sheet_now)
    # plan.save()
    remind_plan(name_list)


